<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
// ------------------------------------------------------------------------

/**
 * Achievements_model class
 *
 * This class abstracts functions for the photo requests
 *
 * @package		Photoggle
 * @subpackage	Models
 * @category	Models
 * @author		Russ
 */
class Achievements_model extends CI_Model {

	/* Achievement bahavior */
	var $table_name = 'a_achievements';
	
	function __construct()
	{
		parent::__construct();
	}


	/**
	 * Fetch all available achievements for a user not including achievements they:
	 * 													- completed
	 *													- have in their TODO list
	 *													- awaiting approval
	 *
	 * @access	public
	 * @return	array
	 */
	function get_available_achievements($user_id, $sort_by, $sort_order)
	{
		$sql = "
				SELECT
					a_achievements.id 			  AS achievementId,
					a_achievements.category_id 	  AS achievementCategoryId,
					a_achievements.points 		  AS achievementPoints,
					a_achievements.title 		  AS achievementTitle,
					a_achievements.description	  AS achievementDescription,
					a_achievements.date_created   AS achievementDateCreated,
					a_achievement_categories.name AS achievementCategoryName
				FROM
					a_achievements
				JOIN
					a_achievement_categories ON a_achievement_categories.id=a_achievements.category_id
				WHERE
					a_achievements.id
						NOT IN
							(
								SELECT
									achievement_id
								FROM
									a_achievements_todo
								WHERE
									user_id = " . $user_id . "
							)
					AND
					a_achievements.id
						NOT IN
							(
								SELECT
									achievement_id
								FROM
									a_achievements_completed
								WHERE
									user_id = " . $user_id . "
							)
				ORDER BY 
					a_achievements." . $sort_by . " " . $sort_order . "
                                ";
		$query = $this->db->query($sql);
		return $query->result();
	}
    /**
	 * Fetch all available achievements for a user not including achievements they:
	 * 													- completed
	 *													- have in their TODO list
	 *													- awaiting approval
         *                                                                                                      - match category chosen
	 *
	 * @access	public
	 * @return	array
	 */
    function get_available_achievements_category($user_id, $category, $sort_by, $sort_order)
	{
		$sql = "
				SELECT
					a_achievements.id 			  AS achievementId,
					a_achievements.category_id 	  AS achievementCategoryId,
					a_achievements.points 		  AS achievementPoints,
					a_achievements.title 		  AS achievementTitle,
					a_achievements.description	  AS achievementDescription,
					a_achievements.date_created   AS achievementDateCreated,
					a_achievement_categories.name AS achievementCategoryName
				FROM
					a_achievements
				JOIN
					a_achievement_categories ON a_achievement_categories.id=a_achievements.category_id
				WHERE
					a_achievements.id
						NOT IN
							(
								SELECT
									achievement_id
								FROM
									a_achievements_todo
								WHERE
									user_id = " . $user_id . "
							)
					AND
					a_achievements.id
						NOT IN
							(
								SELECT
									achievement_id
								FROM
									a_achievements_completed
								WHERE
									user_id = " . $user_id . "
							)
                                         AND
                                         a_achievements.category_id = " . $category . "
                                                
				ORDER BY 
					a_achievements." . $sort_by . " " . $sort_order . "
				";
		$query = $this->db->query($sql);
		return $query->result();
	}
        	/**
	 * Fetch a sorted list of available achievements for a user not including achievements they:
	 * 													- completed
	 *													- have in their TODO list
	 *													- awaiting approval
	 *
	 * @access	public
	 * @return	array
	 */
        function get_available_achievements_sort($user_id, $sort_by, $sort_order)
	{
		$sql = "
				SELECT
					a_achievements.id 			  AS achievementId,
					a_achievements.category_id 	  AS achievementCategoryId,
					a_achievements.points 		  AS achievementPoints,
					a_achievements.title 		  AS achievementTitle,
					a_achievements.description	  AS achievementDescription,
					a_achievements.date_created   AS achievementDateCreated,
					a_achievement_categories.name AS achievementCategoryName
				FROM
					a_achievements
				JOIN
					a_achievement_categories ON a_achievement_categories.id=a_achievements.category_id
				WHERE
					a_achievements.id
						NOT IN
							(
								SELECT
									achievement_id
								FROM
									a_achievements_todo
								WHERE
									user_id = " . $user_id . "
							)
					AND
					a_achievements.id
						NOT IN
							(
								SELECT
									achievement_id
								FROM
									a_achievements_completed
								WHERE
									user_id = " . $user_id . "
							)
				ORDER BY 
					a_achievements." . $sort_by . " " . $sort_order . "
				";
		$query = $this->db->query($sql);
		return $query->result();
	}
		/**
	 * Fetch all available achievements based on search string:
	 * 													
	 *
	 * @access	public
	 * @return	array
	 */
	function get_available_achievements_search($user_id, $search_query, $sort_by, $sort_order)
	{
                $sql = "
				SELECT
					a_achievements.id 			  AS achievementId,
					a_achievements.category_id 	  AS achievementCategoryId,
					a_achievements.points 		  AS achievementPoints,
					a_achievements.title 		  AS achievementTitle,
					a_achievements.description	  AS achievementDescription,
					a_achievements.date_created   AS achievementDateCreated,
					a_achievement_categories.name AS achievementCategoryName
				FROM
					a_achievements
				JOIN
					a_achievement_categories ON a_achievement_categories.id=a_achievements.category_id
				WHERE
					a_achievements.id
						NOT IN
							(
								SELECT
									achievement_id
								FROM
									a_achievements_todo
								WHERE
									user_id = " . $user_id . "
							)
					AND
					a_achievements.id
						NOT IN
							(
								SELECT
									achievement_id
								FROM
									a_achievements_completed
								WHERE
									user_id = " . $user_id . "
							)
                                         AND
                                         ((";
                $count = 0;
                foreach(explode(" ", $search_query) as $value) 
                {
                    if($count > 0)
                    {
                        $sql = $sql . " AND ";
                    }
                    $sql = $sql . "a_achievements.title LIKE '%" . $value . "%'";
                    $count = $count + 1;
                }
                $count = 0;
                $sql = $sql . ")
                               OR (";
                foreach(explode(" ", $search_query) as $value) 
                {
                    if($count > 0)
                    {
                        $sql = $sql . " AND ";
                    }
                    $sql = $sql . "a_achievements.description LIKE '%" . $value . "%'";
                    $count = $count + 1;
                }
                $sql = $sql . "))
				ORDER BY 
					a_achievements." . $sort_by . " " . $sort_order . "
				";
		$query = $this->db->query($sql);
		return $query->result();
	}
	/*
	 * Fetch a sorted list of available achievements based on search string:
	 * 													
	 *
	 * @access	public
	 * @return	array
	 */
    function get_available_achievements_search_sort($user_id, $search_query)
	{
                $sql = "
				SELECT
					a_achievements.id 			  AS achievementId,
					a_achievements.category_id 	  AS achievementCategoryId,
					a_achievements.points 		  AS achievementPoints,
					a_achievements.title 		  AS achievementTitle,
					a_achievements.description	  AS achievementDescription,
					a_achievements.date_created   AS achievementDateCreated,
					a_achievement_categories.name AS achievementCategoryName
				FROM
					a_achievements
				JOIN
					a_achievement_categories ON a_achievement_categories.id=a_achievements.category_id
				WHERE
					a_achievements.id
						NOT IN
							(
								SELECT
									achievement_id
								FROM
									a_achievements_todo
								WHERE
									user_id = " . $user_id . "
							)
					AND
					a_achievements.id
						NOT IN
							(
								SELECT
									achievement_id
								FROM
									a_achievements_completed
								WHERE
									user_id = " . $user_id . "
							)
                                         AND
                                         ((";
                $count = 0;
                foreach(explode(" ", $search_query) as $value) 
                {
                    if($count > 0)
                    {
                        $sql = $sql . " AND ";
                    }
                    $sql = $sql . "a_achievements.title LIKE '%" . $value . "%'";
                    $count = $count + 1;
                }
                $count = 0;
                $sql = $sql . ")
                               OR (";
                foreach(explode(" ", $search_query) as $value) 
                {
                    if($count > 0)
                    {
                        $sql = $sql . " AND ";
                    }
                    $sql = $sql . "a_achievements.description LIKE '%" . $value . "%'";
                    $count = $count + 1;
                }
                $sql = $sql . "))  
				ORDER BY 
					a_achievements." . $sort_by . "
				DESC
				";
		$query = $this->db->query($sql);
		return $query->result();
	}
	/**
	 * Get achievement by id
	 *
	 * @param	string
	 * @return	object
	 */
	function get_achievement_by_id($id)
	{
		$this->db->where('id', $id);

		$query = $this->db->get($this->table_name);
		if ($query->num_rows() == 1) return $query->row();
		return NULL;
	}

 }
 ?>